# -*- coding: utf-8 -*-

"""
数据库管理器
"""

import sqlite3
import os
from datetime import datetime


class DatabaseManager:
    def __init__(self, db_path="data/game.db"):
        """初始化数据库管理器"""
        self.db_path = db_path
        self.init_database()
        
    def init_database(self):
        """初始化数据库表"""
        # 确保数据目录存在
        os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
        
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建玩家表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS players (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建游戏记录表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS game_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                player_id INTEGER,
                difficulty TEXT,
                puzzle_size INTEGER,
                time_spent INTEGER,
                moves_count INTEGER,
                completed BOOLEAN,
                emotion_data TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (player_id) REFERENCES players (id)
            )
        ''')
        
        # 创建设置表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                key TEXT UNIQUE,
                value TEXT,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        conn.commit()
        conn.close()
        
    def add_player(self, name):
        """添加新玩家"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute(
            "INSERT INTO players (name) VALUES (?)",
            (name,)
        )
        
        player_id = cursor.lastrowid
        conn.commit()
        conn.close()
        
        return player_id
        
    def get_player(self, player_id):
        """获取玩家信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute(
            "SELECT * FROM players WHERE id = ?",
            (player_id,)
        )
        
        player = cursor.fetchone()
        conn.close()
        
        return player
        
    def save_game_record(self, player_id, difficulty, puzzle_size, 
                        time_spent, moves_count, completed, emotion_data):
        """保存游戏记录"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO game_records 
            (player_id, difficulty, puzzle_size, time_spent, moves_count, completed, emotion_data)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (player_id, difficulty, puzzle_size, time_spent, moves_count, completed, str(emotion_data)))
        
        record_id = cursor.lastrowid
        conn.commit()
        conn.close()
        
        return record_id
        
    def get_player_stats(self, player_id):
        """获取玩家统计信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 获取总游戏次数和完成次数
        cursor.execute('''
            SELECT COUNT(*) as total_games, 
                   SUM(CASE WHEN completed THEN 1 ELSE 0 END) as completed_games,
                   AVG(time_spent) as avg_time
            FROM game_records 
            WHERE player_id = ?
        ''', (player_id,))
        
        stats = cursor.fetchone()
        conn.close()
        
        if stats:
            total_games, completed_games, avg_time = stats
            success_rate = completed_games / total_games if total_games > 0 else 0
            
            return {
                "total_games": total_games,
                "completed_games": completed_games,
                "success_rate": success_rate,
                "avg_time": avg_time or 0
            }
        
        return {
            "total_games": 0,
            "completed_games": 0,
            "success_rate": 0,
            "avg_time": 0
        }
        
    def save_setting(self, key, value):
        """保存设置"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT OR REPLACE INTO settings (key, value)
            VALUES (?, ?)
        ''', (key, str(value)))
        
        conn.commit()
        conn.close()
        
    def get_setting(self, key, default=None):
        """获取设置"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute(
            "SELECT value FROM settings WHERE key = ?",
            (key,)
        )
        
        result = cursor.fetchone()
        conn.close()
        
        if result:
            return result[0]
        return default